﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
using zUfDsl.Models;
using zUfDsl.ModelUF;

namespace zUfDsl.Common
{
    public class HandleDataGrid : IDisposable
    {
        U8DataContext _db;
        string _reportCode;
        Ext.SuperGridSum _dtv;
        GroupBox _gbFilter;
        System.Drawing.Graphics _graphics;
        private List<reportFilter> _filters;
        private reportMain _repmain;
        private List<reportColumns> _cols;
        public HandleDataGrid(U8DataContext db, string ReportCode, GroupBox gbFilter, Ext.SuperGridSum dtv, System.Drawing.Graphics graphics)
        {
            _db = db;
            _reportCode = ReportCode;
            _gbFilter = gbFilter;
            _dtv = dtv;
            _graphics = graphics;
            dtv.Sorted += dtvSorted;
        }

        public HandleDataGrid(U8DataContext db, GroupBox gbFilter, Ext.SuperGridSum dtv, System.Drawing.Graphics graphics,
            reportMain repmain, List<reportFilter> filters, List<reportColumns> cols)
        {
            _db = db;
            _gbFilter = gbFilter;
            _dtv = dtv;
            _graphics = graphics;
            _dtv.Sorted += dtvSorted;
            _repmain = repmain;
            _filters = filters;
            _cols = cols;
        }

        public HandleDataGrid()
        { }



        public void dtvSorted(object sender, EventArgs e)
        {
            AddRowIndex(_dtv);
        }

        public void Dispose()
        {
        }



        /// <summary>
        /// 获取列配置
        /// </summary>
        /// <returns></returns>
        public List<reportColumns> GetColSet()
        {
            if (_cols == null)
            {
                _cols = new List<reportColumns>();
                var tmpcolindex = _cols.FirstOrDefault(s => s.Code == "rowindex");
                if (tmpcolindex == null)
                {
                    tmpcolindex = new reportColumns() { Code = "rowindex", Title = "行号", Align = 2, Width = 60, bModify = false, iOrder = -2 };
                    _cols.Add(tmpcolindex);
                }
                var tmpcball = _cols.FirstOrDefault(s => s.Code == "cball");
                if (tmpcball == null)
                {
                    tmpcball = new reportColumns() { Code = "cball", Title = "cball", Align = 2, Width = 50, bModify = true, iOrder = -1 };
                    _cols.Add(tmpcball);
                }
                _cols = _cols.OrderBy(s => s.iOrder).ToList();
            }
            return _cols;
        }

        /// <summary>
        /// 获取过滤条件配置
        /// </summary>
        /// <returns></returns>
        private List<reportFilter> GetFilter()
        {
            if (_filters == null)
            {
                _filters = new List<reportFilter>();
            }
            return _filters;
        }

        #region 初始化过滤条件
        /// <summary>
        /// 初始化过滤条件
        /// </summary>
        public void InitFilter()
        {
            _gbFilter.Controls.Clear();
            var filter = GetFilter();
            int ileft = 10;
            int itop = 20;
            for (int k = 0; k < filter.Count(); k++)
            {
                var t = filter[k];
                var defaultVal = GetDefaultValue(t.FieldDefaultValue);
                if (t.FieldWidth <= 0) { continue; }
                var label = new Label()
                {
                    Name = $"{t.FieldCode}tip",
                    Text = t.FieldTitle,
                    Visible = true,
                    Height = 30,
                    AutoSize = false,
                    Font = new System.Drawing.Font("宋体", 9)
                };
                var size = _graphics.MeasureString(t.FieldTitle, new System.Drawing.Font("宋体", 9));
                label.Width = Convert.ToInt32(Math.Floor(size.Width)) + 5;
                Control cbinput;
                if (t.ControlType == "date")
                {
                    if (t.FieldWidth.ObjToInt() < 110) { t.FieldWidth = 110; }
                    var dt = new DateTimePicker()
                    {
                        Name = t.FieldCode,
                        Width = t.FieldWidth.ObjToInt(),
                        Visible = true,
                        Format = DateTimePickerFormat.Custom,
                        CustomFormat = "yyyy-MM-dd",
                        ShowCheckBox = true
                    };
                    if (defaultVal.ObjToString().Length > 0) { dt.Value = defaultVal.ObjToDate(); dt.Checked = true; }
                    else { dt.Value = DateTime.Now.Date; dt.Checked = false; }
                    cbinput = dt;
                }
                else if (t.ControlType == "select" || t.ControlType == "SelectText" || t.ControlType == "AllYesNo")
                {
                    var cb = new ComboBox()
                    {
                        Name = t.FieldCode,
                        Width = t.FieldWidth.ObjToInt(),
                        Visible = true,
                        DropDownStyle = ComboBoxStyle.DropDownList
                    };
                    string enumkeyword;
                    if (t.ControlType == "AllYesNo")
                    {
                        enumkeyword = "1|是;0|否";
                    }
                    else
                    {
                        enumkeyword = t.EnumKeyWord;
                    }
                    InitControlCombol(cb, enumkeyword, defaultVal, t.bDisplayAll.ObjToBool());
                    cbinput = cb;
                }
                else
                {
                    var tb = new TextBox() { Name = t.FieldCode, Width = t.FieldWidth.ObjToInt(), Visible = true, Height = 30 };
                    if (defaultVal.ObjToString().Length > 0) { tb.Text = defaultVal; }
                    cbinput = tb;
                }

                var itotolwidth = label.Width + cbinput.Width;
                if (ileft != 10 & ileft + itotolwidth > _gbFilter.Width)
                {
                    ileft = 10; itop += 30;
                }
                label.Top = itop;
                label.Left = ileft;
                cbinput.Left = ileft + label.Width + 5;
                cbinput.Top = itop;
                ileft = cbinput.Left + cbinput.Width + 10;
                _gbFilter.Controls.Add(label);
                _gbFilter.Controls.Add(cbinput);
            }

        }
        #endregion



        #region 设置下拉列表
        /// <summary>
        /// 设置下拉列表
        /// </summary>
        public void InitControlCombol(ComboBox cb, string controlvalue,
            string cDefaultVal, bool bAll, bool defaultValueFromKey = true)
        {
            var selectindex = 0;
            if (bAll)
            {
                ComboboxValue o = new ComboboxValue() { key = "", value = "全部" };
                cb.Items.Add(o);
            }
            var lval = controlvalue.Split(';');
            for (int i = 0; i < lval.Count(); i++)
            {
                var v = lval[i];
                var tt = v.Split('|');
                ComboboxValue o;
                if (tt.Count() >= 2)
                {
                    o = new ComboboxValue { key = tt[0], value = tt[1] };
                }
                else
                {
                    o = new ComboboxValue { key = tt[0], value = tt[0] };
                }
                if (o.key == cDefaultVal)
                { selectindex = bAll == false ? i : i + 1; }
                cb.Items.Add(o);
            }
            //cb.DataSource = dt;
            cb.DisplayMember = "value";
            cb.ValueMember = "key";
            { cb.SelectedIndex = selectindex; }
        }
        #endregion

        #region 初始化DataGridView
        /// <summary>
        /// 初始化DataGridView
        /// </summary>
        public void InitGrid(Ext.SuperGridSum dgv, List<reportColumns> cols)
        {
            _cols = cols;
            _dtv = dgv;
            _dtv.RowHeadersVisible = false;
            _dtv.DataSource = null;
            _dtv.Rows.Clear();
            _dtv.Columns.Clear();
            _dtv.AutoGenerateColumns = false;
            _dtv.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            _dtv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing;
            _dtv.ColumnHeadersHeight = 30;


            for (int i = 0; i < _cols.Count; i++)
            {
                var t = _cols[i];
                DataGridViewColumn column;
                switch (t.Code.ObjToString())
                {
                    case "rowindex":
                        column = new DataGridViewTextBoxColumn();
                        column.Frozen = true;
                        break;
                    case "cball":
                        column = new DataGridViewCheckBoxColumn();
                        var cbHeader = new DatagridViewCheckBoxHeaderCell();
                        cbHeader.Value = string.Empty;
                        //cbHeader.OnCheckBoxClicked += new Ext.CheckBoxClickedHandler(cbHeaderOnCheckBoxClicked);
                        column.HeaderCell = cbHeader;
                        column.Frozen = true;
                        column.ReadOnly = false;
                        break;
                    default:
                        column = new DataGridViewTextBoxColumn();
                        break;
                }

                column.DataPropertyName = t.Code;
                column.Name = t.Code;
                if (t.Code != "cball")
                {
                    column.HeaderText = t.Title;
                }
                switch (t.Align.ObjToInt())
                {
                    case 1:
                        column.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                        break;
                    case 2:
                        column.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        break;
                    case 3:
                        column.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                        break;
                }
                column.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                column.Width = t.Width.ObjToInt();
                if (t.Width <= 0 || t.Hidden == 1)
                {
                    column.Visible = false;
                }
                if (t.cFormat.ObjToString().Length > 0)
                {
                    column.DefaultCellStyle.Format = t.cFormat;
                }
                if (t.bModify.ObjToBool())
                {
                    column.ReadOnly = false;
                }
                else { column.ReadOnly = true; }
                if (column.Name == "rowindex")
                {
                    //column.DataPropertyName = null;
                    column.ReadOnly = true;
                }
                column.DisplayIndex = i;
                if(t.iSort==0)
                {
                    column.SortMode = DataGridViewColumnSortMode.Automatic;
                }
                else
                {
                    column.SortMode = DataGridViewColumnSortMode.NotSortable;
                }
                column.Tag = t;
                if (column.Frozen)
                {
                    for (int j = 0; j < _dtv.Columns.Count; j++)
                    {
                        var t1 = _dtv.Columns[j];
                        t1.Frozen = true;
                    }
                }
                _dtv.Columns.Add(column);
            }
            //dtv.IsShowSumRow = true;

        }
        #endregion


        #region 显示数据
        public void GetData(reportMain repmain)
        {
            #region 获取SQL语句

            var tmptable = $"tmp{DateTime.Now.ToString("yyyyMMddHHmmssfff")}";

            var sqlcol = "";
            var lcolset = GetColSet();
            for (int i = 0; i < lcolset.Count; i++)
            {
                var t = lcolset[i];
                var columncode = t.Code.ToLower();
                if (columncode == "cball" || columncode == "rowindex" || columncode == "checkbox") { continue; }
                else { sqlcol += $" {t.DataSource} as {t.Code},"; }
            }
            if (sqlcol.Length == 0) { return; }
            sqlcol = sqlcol.Substring(0, sqlcol.Length - 1);
            var sql = $" select {sqlcol} into tempdb..{tmptable} {repmain.SqlStr} ";
            Dictionary<string, string> dic;
            var w = GetFilterValue(out dic);
            sql = $"exec spexecutesql N'{sql} where {w}',N'";
            var para = "";
            foreach (var key in dic)
            {
                sql += $"@{key.Key} nvarchar(500),";
                var tmpfilter = GetFilter().FirstOrDefault(s => s.FieldCode == key.Key);
                if (tmpfilter != null)
                {
                    para += $"@{key.Key}='{string.Format(tmpfilter.ValueFormat, key.Value)}',";
                }
            }
            if (para.Length > 0)
            {
                sql = $"{sql.Substring(0, sql.Length - 1)}', {para.Substring(0, para.Length - 1)}";
            }
            else
            {
                sql = sql.Substring(0, sql.Length - 3);
            }
            #endregion
            #region 获取数据
            _db.ExecuteCommand(sql);
            sql = $"select * from tempdb..{tmptable}";
            var dt = _db.GetDataTable(sql);
            _dtv.DataSource = dt;
            AddRowIndex(_dtv);
            #region 合计行
            sql = $"select top 1 {GetColumnsSum()} from tempdb..{tmptable}";
            var dtsum = _db.GetDataTable(sql);
            //dtv.InitSum();
            _dtv.SetSum(dtsum);
            #endregion 
            sql = $"drop table tempdb..{tmptable}";
            _db.ExecuteCommand(sql);
            #endregion

        }
        #endregion


        #region 获取合计列的数据
        /// <summary>
        /// 获取合计列的数据
        /// </summary>
        /// <param name="Columns"></param>
        /// <returns></returns>
        private string GetColumnsSum()
        {
            string sColumns = "";
            var lcolset = GetColSet();
            for (int i = 0; i < lcolset.Count; i++)
            {
                var colum = lcolset[i];
                var columncode = colum.Code.ToLower();
                if (columncode == "cball" || columncode == "rowindex" || columncode == "checkBox") { continue; }
                if (colum.FootCount != null && colum.FootCount.Length > 0)
                {
                    if (colum.FootCount.StartsWith("@"))
                    {
                        sColumns += $"{colum.FootCount.Substring(1, colum.FootCount.Length - 1)} as [{colum.Code}],";
                    }
                    else
                    {
                        sColumns += $"{colum.FootCount}({colum.DataSource}) as [{colum.Code}],";
                    }
                }
                else
                {
                    sColumns += $"null as [{colum.Code}],";
                }

            }
            sColumns = sColumns.Trim(',');
            return sColumns;
        }
        #endregion 获取合计列的数据

        #region 绑定数据
        public void BlindData<T>(List<T> list)
        {
            var dt = new DataTable();
            var type = typeof(T);
            var ps = type.GetProperties();
            for (int j = 0; j < _cols.Count; j++)
            {
                var t = _cols[j];
                var dtc = new DataColumn() { ColumnName = t.Code, Caption = t.Title };
                var tmpps = ps.FirstOrDefault(s => s.Name == t.Code);
                if (tmpps != null)
                {
                    if (tmpps.PropertyType == typeof(int) || tmpps.PropertyType == typeof(int?))
                    { dtc.DataType = typeof(int); }
                    if (tmpps.PropertyType == typeof(double) || tmpps.PropertyType == typeof(double?))
                    { dtc.DataType = typeof(double); }
                    if (tmpps.PropertyType == typeof(decimal) || tmpps.PropertyType == typeof(decimal?))
                    { dtc.DataType = typeof(double); }
                    else
                    {
                        dtc.DataType = typeof(string);
                    }
                }
                else
                {
                    dtc.DataType = typeof(string);
                }
                dt.Columns.Add(dtc);
            }
            for (int i = 0; i < list.Count; i++)
            {
                var t1 = list[i];
                var dtr = dt.NewRow();
                for (int j = 0; j < _cols.Count; j++)
                {
                    var t2 = _cols[j];
                    var p = ps.FirstOrDefault(s => s.Name == t2.Code);
                    if (p != null)
                    {
                        var obj = p.GetValue(t1, null);
                        if (obj != null)
                        {
                            dtr[t2.Code] = obj;
                        }
                    }
                }
                dt.Rows.Add(dtr);
            }
            _dtv.DataSource = dt;
            AddRowIndex(_dtv);
        }
        #endregion

        #region 绑定数据
        public void BlindData<T>(T obj, DataGridViewRow dtr)
        {
            var type = typeof(T);
            var ps = type.GetProperties();
            for (int j = 0; j < _dtv.Columns.Count; j++)
            {
                var t = _dtv.Columns[j];
                var p = ps.FirstOrDefault(s => s.Name == t.DataPropertyName);
                if (p != null)
                {
                    var oval = p.GetValue(obj, null);
                    if (oval != null)
                    {
                        dtr.Cells[t.DataPropertyName].Value = oval;
                    }
                }
            }
        }
        #endregion

        #region 增加列号
        public void AddRowIndex(DataGridView dgv)
        {
            if (!dgv.Columns.Contains("rowindex")) { return; }
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                dgv.Rows[i].Cells["rowindex"].Value = (i + 1).ToString();
            }
        }
        #endregion 

        #region 绑定数据
        public void DisplayData<T>(DataGridView dgv, List<T> list)
        {
            dgv.Rows.Clear();
            if (list.Count == 0) { return; }
            dgv.Rows.Add(list.Count);
            var type = typeof(T);
            var lset = type.GetProperties();
            for (int i = 0; i < list.Count; i++)
            {
                var t1 = list[i];
                var dgr = dgv.Rows[i];
                for (int j = 0; j < lset.Count(); j++)
                {
                    var pset = lset[j];
                    if (!dgv.Columns.Contains(pset.Name)) { continue; }
                    var val = pset.GetValue(t1, null);
                    dgr.Cells[pset.Name].Value = val;
                }
            }
            AddRowIndex(dgv);
        }
        #endregion 


        public List<T> GridSelRowToList<T>()
        {
            _dtv.EndEdit();
            var lrow = new List<DataGridViewRow>();
            for (int i = 0; i < _dtv.Rows.Count; i++)
            {
                var dtr = _dtv.Rows[i];
                if (dtr.Cells["cball"].Value.ObjToBool())
                {
                    lrow.Add(dtr);
                }
            }
            return GridRowToList<T>(lrow);
        }
        public List<T> GridRowToList<T>()
        {
            var lrow = new List<DataGridViewRow>();
            for (int i = 0; i < _dtv.Rows.Count; i++)
            {
                var dtr = _dtv.Rows[i];
                lrow.Add(dtr);
            }
            return GridRowToList<T>(lrow);
        }

        public List<T> GridRowToList<T>(List<DataGridViewRow> dtrs)
        {
            PropertyInfo[] ps = typeof(T).GetProperties();
            var list = new List<T>();
            if (dtrs.Count == 0) { return list; }
            var dtr = dtrs[0];
            var lcolumns = dtr.DataGridView.Columns;
            var lcolumnnane = new List<string>();
            for (int i = 0; i < lcolumns.Count; i++)
            {
                lcolumnnane.Add(lcolumns[i].Name);
            }
            for (int i = 0; i < dtrs.Count; i++)
            {
                dtr = dtrs[i];
                var obj = Activator.CreateInstance<T>();
                foreach (PropertyInfo p in ps)
                {
                    string pname = p.Name;
                    if (lcolumnnane.Contains(pname))
                    {
                        var val = dtr.Cells[pname].Value;
                        if (val == DBNull.Value) { continue; }
                        var bset = false;
                        if (p.PropertyType == typeof(decimal) || p.PropertyType == typeof(decimal?))
                        { p.SetValue(obj, val.ObjToDecimal(), null); bset = true; }
                        if (p.PropertyType == typeof(int) || p.PropertyType == typeof(int?))
                        { p.SetValue(obj, val.ObjToInt(), null); bset = true; }
                        if (p.PropertyType == typeof(DateTime) || p.PropertyType == typeof(DateTime?))
                        { p.SetValue(obj, val.ObjToDate1(), null); bset = true; }
                        if (!bset)
                        { p.SetValue(obj, val, null); }
                    }
                }
                list.Add(obj);
            }
            return list;
        }

        public T GridRowToObj<T>(DataGridViewRow dtr)
        {
            PropertyInfo[] ps = typeof(T).GetProperties();
            var lcolumns = dtr.DataGridView.Columns;
            var lcolumnnane = new List<string>();
            for (int i = 0; i < lcolumns.Count; i++)
            {
                lcolumnnane.Add(lcolumns[i].Name);
            }
            var obj = Activator.CreateInstance<T>();
            foreach (PropertyInfo p in ps)
            {
                string pname = p.Name;
                if (lcolumnnane.Contains(pname))
                {
                    var val = dtr.Cells[pname].Value;
                    if (val == DBNull.Value) { val = null; }
                    var bset = false;
                    if (p.PropertyType == typeof(decimal) || p.PropertyType == typeof(decimal?))
                    { p.SetValue(obj, val.ObjToDecimal(), null); bset = true; }
                    if (p.PropertyType == typeof(int) || p.PropertyType == typeof(int?))
                    { p.SetValue(obj, val.ObjToInt(), null); bset = true; }
                    if (p.PropertyType == typeof(DateTime) || p.PropertyType == typeof(DateTime?))
                    { p.SetValue(obj, val.ObjToDate1(), null); bset = true; }
                    if (!bset)
                    { p.SetValue(obj, val, null); }
                }
            }
            return obj;
        }


        #region 导出Excel
        #region dataGridView的数据导出Excel格式
        /// <summary>
        /// dataGridView的数据导出Excel格式
        /// </summary>
        /// <param name="dv"></param>
        public void ExportExcel(string filenameori)
        {
            #region 保存的文件
            SaveFileDialog sf = new SaveFileDialog() { FileName = filenameori };
            sf.Filter = "xlsx文件(*.xlsx)|*.xlsx";
            DialogResult dr = sf.ShowDialog();
            if (dr != DialogResult.OK) return;
            string filename = sf.FileName;
            if (filename.Substring(filename.Length - 4, 4) == "xlsx")
            {
                filename = filename.Substring(0, filename.Length - 1);
            }
            #endregion
            //tb.Text = "正在格式化表头";
            #region 数据转换为html
            StringBuilder sb = new StringBuilder();
            sb.Append("<html><head><meta content=\"text / html; charset = gb2312\"></head><table border=1><tr>");
            for (int k = 0; k < _dtv.Columns.Count; k++)
            {
                var dgc = _dtv.Columns[k];
                if (dgc.HeaderText != string.Empty)
                {
                    if (dgc.Visible)
                    {
                        sb.Append(string.Format("<th>{0}</th>", _dtv.Columns[k].HeaderText));
                    }
                }
            }
            sb.Append("</tr>");
            //sb.Append(Environment.NewLine);
            for (int i = 0; i < _dtv.Rows.Count; i++)
            {
                //tb.Text = string.Format("导出中[{0}/{1}]", i, dv.Rows.Count);
                sb.Append("<tr>");
                for (int j = 0; j < _dtv.Columns.Count; j++)
                {
                    var dgc = _dtv.Columns[j];
                    if (dgc.HeaderText == string.Empty) { continue; }
                    if (!dgc.Visible) { continue; }
                    object oval = _dtv.Rows[i].Cells[j].Value;
                    if (oval == null || oval == DBNull.Value) oval = "";
                    string sretrun;
                    //switch (dv.Columns[j].DefaultCellStyle.Format)
                    //{
                    //    case "yyyy-MM-dd":
                    //        DateTime dt0;
                    //        if (DateTime.TryParse(oval.ToString(), out dt0))
                    //        { sretrun = dt0.ToString("yyyy-MM-dd"); }
                    //        else
                    //        { sretrun = oval.ToString(); }
                    //        break;
                    //    //case "0":
                    //    //    sretrun = "'" + oval.ToString();
                    //    //    break;
                    //    case "#,##0.00":
                    //        double dval;
                    //        if (double.TryParse(oval.ToString(), out dval))
                    //        {
                    //            sretrun = dval.ToString("#,##0.00");
                    //        }
                    //        else
                    //        { sretrun = oval.ToString(); }
                    //        break;
                    //    default:
                    //        sretrun = oval.ToString();
                    //        break;

                    sretrun = oval.ToString();
                    var dtc = _dtv.Columns[j];
                    string cformat = dtc.DefaultCellStyle.Format;
                    string excelFormat = "";
                    if (cformat.Length > 0) { excelFormat = string.Format("style='mso-number-format: \"{0}\"; '", cformat); }
                    else
                    {
                        excelFormat = string.Format("style='mso-number-format: \"@\"; '");
                    }

                    sb.Append(string.Format("<td {1}>{0}</td>", sretrun, excelFormat));
                    //sb.Append(dt.Rows[i][j].ToString() + "\t");
                }
                sb.Append("</tr>");
                //sb.Append(Environment.NewLine);
            }
            System.IO.StreamWriter writer1 = new System.IO.StreamWriter(filename, false, Encoding.Default);
            writer1.Write(sb.ToString());
            writer1.Close();
            #endregion

            #region 使用Excel打开，然后另存一下
            string filename1;
            //tb.Text = string.Format("保存中");
            var hwnd = HandleExcel(filename, out filename1);
            //tb.Text = string.Format("保存完成");
            KillExcel(hwnd);
            #endregion

            #region 打开Excel文件
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            System.IO.File.Delete(filename);
            dr = MessageBox.Show(string.Format("保存成功，文件保存在{0}。是否立即打开？", filename1), "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
            if (dr == DialogResult.Yes)
            {
                System.Diagnostics.Process process = new System.Diagnostics.Process();
                process.StartInfo.FileName = filename1;
                process.Start();
            }
            #endregion
        }
        #endregion

        #region dataGridView的数据导出Excel格式
        /// <summary>
        /// dataGridView的数据导出Excel格式
        /// </summary>
        /// <param name="dv"></param>
        public void ExportExcel(DataGridView dv, Button tb, string filenameori)
        {
            #region 保存的文件
            SaveFileDialog sf = new SaveFileDialog() { FileName = filenameori };
            sf.Filter = "xlsx文件(*.xlsx)|*.xlsx";
            DialogResult dr = sf.ShowDialog();
            if (dr != DialogResult.OK) return;
            string filename = sf.FileName;
            if (filename.Substring(filename.Length - 4, 4) == "xlsx")
            {
                filename = filename.Substring(0, filename.Length - 1);
            }
            #endregion
            tb.Text = "正在格式化表头";
            #region 数据转换为html
            StringBuilder sb = new StringBuilder();
            sb.Append("<html><head><meta content=\"text / html; charset = gb2312\"></head><table border=1><tr>");
            for (int k = 0; k < dv.Columns.Count; k++)
            {
                var dgc = dv.Columns[k];
                if (dgc.HeaderText != string.Empty)
                {
                    if (dgc.Visible)
                    {
                        sb.Append(string.Format("<th>{0}</th>", dv.Columns[k].HeaderText));
                    }
                }
            }
            sb.Append("</tr>");
            //sb.Append(Environment.NewLine);
            for (int i = 0; i < dv.Rows.Count; i++)
            {
                tb.Text = string.Format("导出中[{0}/{1}]", i, dv.Rows.Count);
                sb.Append("<tr>");
                for (int j = 0; j < dv.Columns.Count; j++)
                {
                    var dgc = dv.Columns[j];
                    if (dgc.HeaderText == string.Empty) { continue; }
                    if (!dgc.Visible) { continue; }
                    object oval = dv.Rows[i].Cells[j].Value;
                    if (oval == null || oval == DBNull.Value) oval = "";
                    string sretrun;
                    //switch (dv.Columns[j].DefaultCellStyle.Format)
                    //{
                    //    case "yyyy-MM-dd":
                    //        DateTime dt0;
                    //        if (DateTime.TryParse(oval.ToString(), out dt0))
                    //        { sretrun = dt0.ToString("yyyy-MM-dd"); }
                    //        else
                    //        { sretrun = oval.ToString(); }
                    //        break;
                    //    //case "0":
                    //    //    sretrun = "'" + oval.ToString();
                    //    //    break;
                    //    case "#,##0.00":
                    //        double dval;
                    //        if (double.TryParse(oval.ToString(), out dval))
                    //        {
                    //            sretrun = dval.ToString("#,##0.00");
                    //        }
                    //        else
                    //        { sretrun = oval.ToString(); }
                    //        break;
                    //    default:
                    //        sretrun = oval.ToString();
                    //        break;

                    sretrun = oval.ToString();
                    var dtc = dv.Columns[j];
                    string cformat = dtc.DefaultCellStyle.Format;
                    string excelFormat = "";
                    if (cformat.Length > 0) { excelFormat = string.Format("style='mso-number-format: \"{0}\"; '", cformat); }
                    else
                    {
                        excelFormat = string.Format("style='mso-number-format: \"@\"; '");
                    }

                    sb.Append(string.Format("<td {1}>{0}</td>", sretrun, excelFormat));
                    //sb.Append(dt.Rows[i][j].ToString() + "\t");
                }
                sb.Append("</tr>");
                //sb.Append(Environment.NewLine);
            }
            System.IO.StreamWriter writer1 = new System.IO.StreamWriter(filename, false, Encoding.Default);
            writer1.Write(sb.ToString());
            writer1.Close();
            #endregion

            #region 使用Excel打开，然后另存一下
            string filename1;
            tb.Text = string.Format("保存中");
            var hwnd = HandleExcel(filename, out filename1);
            tb.Text = string.Format("保存完成");
            KillExcel(hwnd);
            #endregion

            #region 打开Excel文件
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            System.IO.File.Delete(filename);
            dr = MessageBox.Show(string.Format("保存成功，文件保存在{0}。是否立即打开？", filename1), "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
            if (dr == DialogResult.Yes)
            {
                System.Diagnostics.Process process = new System.Diagnostics.Process();
                process.StartInfo.FileName = filename1;
                process.Start();
            }
            #endregion
        }
        #endregion

        private int HandleExcel(string filename, out string filename1)
        {
            // Get the class type and instantiate Excel.
            var objClassType = Type.GetTypeFromProgID("Excel.Application");
            var objApp = Activator.CreateInstance(objClassType);
            var Parameters = new object[1];
            Parameters[0] = false;
            objClassType.InvokeMember("Visible", BindingFlags.SetProperty, null, objApp, Parameters);
            objClassType.InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, objApp, Parameters);
            var excelworkbooks = objClassType.InvokeMember("Workbooks", BindingFlags.GetProperty, null, objApp, null);
            Parameters = new object[1];
            Parameters[0] = filename;
            var workbook = excelworkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, excelworkbooks, Parameters);
            filename1 = filename + "x";
            Parameters = new object[2];
            Parameters[0] = filename1;
            Parameters[1] = 51;
            workbook.GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod, null, workbook, Parameters);
            objClassType.InvokeMember("Quit", BindingFlags.InvokeMethod, null, objApp, null);
            var hwnd = objClassType.InvokeMember("Hwnd", BindingFlags.GetProperty, null, objApp, null);
            return hwnd.ObjToInt();
        }

        #region 强制退掉Excel进程
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        /// <summary>
        /// 强制退出进程
        /// </summary>
        /// <param name="hwnd"></param>
        private void KillExcel(int hwnd)
        {

            IntPtr t = new IntPtr(hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();
        }


        #endregion
        #endregion

        #region 获取默认值
        /// <summary>
        /// 获取默认值
        /// </summary>
        private string GetDefaultValue(string val)
        {
            if (val == null || val.Length == 0) { return ""; }
            if (val.Substring(0, 1) != "@") { return val; }

            switch (val)
            {
                case "@cPersonName":
                    return cHandleUF.cUserName;
                case "@cPersonCode":
                    return cHandleUF.cUserID;
                case "@today":
                    return cHandleUF.dLoginDate.ToString("yyyy-MM-dd");
                case "@yearstart":
                    return new DateTime(cHandleUF.dLoginDate.Year, 1, 1).ToString("yyyy-MM-dd");
                case "@yearend":
                    return new DateTime(cHandleUF.dLoginDate.Year + 1, 1, 1).AddDays(-1).ToString("yyyy-MM-dd");
                case "@yearstartlast":
                    return new DateTime(cHandleUF.dLoginDate.Year - 1, 1, 1).ToString("yyyy-MM-dd");
                case "@yearendlast":
                    return new DateTime(cHandleUF.dLoginDate.Year, 1, 1).AddDays(-1).ToString("yyyy-MM-dd");
                case "@monthstart":
                    var d = DateTime.Now;
                    var mstart = new DateTime(d.Year, d.Month, 1);
                    return mstart.ToString("yyyy-MM-dd");
                case "@monthend":
                    var d1 = DateTime.Now;
                    var mstart1 = new DateTime(d1.Year, d1.Month, 1);
                    return mstart1.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
                case "@temptable":
                    return "tpr" + DateTime.Now.ToString("HHmmssfff") + "t";
                case "@year":
                    return DateTime.Now.Year.ToString();
                case "@lastmonth":
                    return DateTime.Now.AddMonths(-1).ToString("yyyy-MM");
                case "@lastmonthstart":
                    return new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1).ToString("yyyy-MM-dd");
                case "@lastmonthend":
                    return new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddDays(-1).ToString("yyyy-MM-dd");
                case "@month":
                    return DateTime.Now.ToString("yyyy-MM");
                default:
                    return val;
            }
        }
        #endregion

        #region 计算查询条件
        /// <summary>
        /// 计算查询条件
        /// </summary>
        private string GetFilterValue(out Dictionary<string, string> dic)
        {
            var w = " 1=1 ";
            var filters = GetFilter();
            dic = new Dictionary<string, string>();
            foreach (Control c in _gbFilter.Controls)
            {
                if (c.GetType() == typeof(Label)) { continue; }
                var tfilter = filters.FirstOrDefault(s => s.FieldCode == c.Name);
                var key = ""; var value = "";
                if (tfilter == null) { continue; }
                else if (c.GetType() == typeof(DateTimePicker))
                {
                    var dt = (DateTimePicker)c;
                    if (dt.Checked)
                    {
                        key = tfilter.FieldCode;
                        value = dt.Value.ToString("yyyy-MM-dd");
                    }
                }
                else if (c.GetType() == typeof(ComboBox))
                {
                    var cb = (ComboBox)c;
                    if (cb.SelectedItem != null)
                    {
                        var selitm = (ComboboxValue)cb.SelectedItem;
                        key = tfilter.FieldCode;
                        value = selitm.key;
                    }
                }
                else if (c.GetType() == typeof(TextBox))
                {
                    var tb = (TextBox)c;
                    if (tb.Text.Trim().Length > 0)
                    {
                        key = tfilter.FieldCode;
                        value = tb.Text.Trim();
                    }
                }
                if (key.Length > 0 && value.Length > 0)
                {
                    dic.Add(key, value);
                    switch (tfilter.ModeEx)
                    {
                        case "exp":
                            w += $" and {tfilter.FieldName} ";
                            break;
                        default:
                            w += $" and {tfilter.FieldName} {tfilter.ModeEx} @{tfilter.FieldCode}";
                            break;
                    }
                }
            }
            return w;
        }
        #endregion

        #region 下拉列表的值
        /// <summary>
        /// 下拉列表的值
        /// </summary>
        public class ComboboxValue
        {
            public string key { set; get; }
            public string value { set; get; }
        }
        #endregion

        #region 计算查询条件
        /// <summary>
        /// 计算查询条件
        /// </summary>
        public string GetControlValue(Control c)
        {
            if (c.GetType() == typeof(DateTimePicker))
            { return ((DateTimePicker)c).Value.ToString("yyyy-MM-dd"); }
            if (c.GetType() == typeof(ComboBox))
            {
                var cb = (ComboBox)c;
                if(cb.SelectedItem!=null)
                {
                    var obj = (ComboboxValue)cb.SelectedItem;
                    return obj.key;
                }
                else
                {
                    return cb.SelectedValue.ObjToString();
                }
            }
            if (c.GetType() == typeof(TextBox))
            { return ((TextBox)c).Text.Trim(); }
            if(c.GetType()==typeof(CheckBox))
            {
                return ((CheckBox)c).Checked.ToString();
            }
            return "";
        }
        #endregion
    }
}
